import random
import time
import xlrd
import pymysql

# 连接数据库
import xlwt

connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='shop',
    charset='utf8'
)


def test():
    rbook2 = xlrd.open_workbook(r'C:\Users\da\Desktop\最终售价.xls')
    rbook2.sheets()
    rsheet2 = rbook2.sheet_by_index(0)

    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet('sheet 1')

    count = 0
    for row in rsheet2.get_rows():
        if row[0].value != '商品名称':
            for row2 in rsheet2.get_rows():
                if row[0].value == row2[0].value:
                    print(row[0].value)
                    if row[15].value != '' and row2[15].value != '':
                        print('规格一  ' + str(float(row2[15].value) - float(row[15].value)))
                    if row[17].value != '' and row2[17].value != '':
                        print('规格二  ' + str(float(row2[17].value) - float(row[17].value)))
                    if row[19].value != '' and row2[19].value != '':
                        print('规格三  ' + str(float(row2[19].value) - float(row[19].value)))
                    print('-----------------------------------------------------------------')
                    count = count + 1
                    break
    wbk.save(r'C:\Users\da\Desktop\123.xls')


def priceGap(oldPrice):
    # 设置盈利空间

    if oldPrice < 10:
        oldPrice = oldPrice + 3
    elif 10 <= oldPrice < 50:
        oldPrice = oldPrice + 5
    elif 50 <= oldPrice < 80:
        oldPrice = oldPrice + 10
    elif 80 <= oldPrice:
        oldPrice = oldPrice + 15
    return oldPrice


if __name__ == '__main__':
    rbook2 = xlrd.open_workbook(r'C:\Users\da\Desktop\1.xls')
    rbook2.sheets()
    rsheet2 = rbook2.sheet_by_index(0)

    count = 0
    cursor = connect.cursor()
    for row in rsheet2.get_rows():
        if row[0].value != '商品名称':
            show = "select goods_id from ecs_goods where goods_name='" + row[0].value + "'"
            cursor.execute(show)
            allAttr = cursor.fetchall()
            if len(allAttr) != 0:
                change = "update ecs_goods set shop_price='" + str(int(row[14].value)) + "',market_price='" + str(
                    int(row[15].value)) + "' where goods_id='" + str(allAttr[0][0]) + "'"
                cursor.execute(change)
                print(change)
